from pyparsing import Literal, CaselessLiteral, Word, Upcase 
from pyparsing import delimitedList, Optional, Combine, Group, Forward
from pyparsing import alphas, nums, alphanums
from pyparsing import oneOf, quotedString, ZeroOrMore, restOfLine
from pyparsing import ParseException

class SQLGrammar:

    def __init__(self):
        # define SQL tokens
        sqlStmt = Forward()
        selectToken = CaselessLiteral( "select" )
        fromToken   = CaselessLiteral( "from" )
        insertToken = CaselessLiteral( "insert" )
        intoToken   = CaselessLiteral( "into" )
        valueToken  = CaselessLiteral( "values" )
        createToken = CaselessLiteral( "create" )
        dropToken   = CaselessLiteral( "drop" )

        ident          = Word( alphas, alphanums + "_$" ).setName("identifier")
        columnName     = Upcase( delimitedList( ident, ".", combine=True ) )
        columnNameList = Group( delimitedList( columnName ) )
        tableName      = Upcase( delimitedList( ident, ".", combine=True ) )
        tableNameList  = Group( delimitedList( tableName ) )
        valueName      = Upcase( delimitedList( ident, ".", combine=True ) )
        valueNameList  = Group( delimitedList( valueName | quotedString))

        varTypeName = oneOf("char CHAR varchar VARCHAR smallint SMALLINT numeric NUMERIC tinyint TINYINT decimal DECIMAL date DATE int INT") + Optional("(" + Word(alphanums) + ")")

        legalPathChars = "~/_-().?,;"
	ident          = Word( alphas, alphanums + "_$" ).setName("identifier")
        typeName       = delimitedList( ident, ".", combine=True ) 
        typeNameList   = Group( delimitedList( typeName ) )
        idName         = Upcase( delimitedList( ident, ".", combine=True ) )
        idNameList     = Group( delimitedList( idName ) )
       
        varType         = Group(ident + varTypeName)
	varTypeName     = delimitedList(varType,",")
	varTypeNameList = Group(delimitedList(varTypeName))
    
	pathident    =  Word(alphas + alphanums +  legalPathChars).setName("identifier")
	pathName     =  delimitedList( pathident,".", combine=True )
	pathNameList = Group( delimitedList( pathName ) )

        whereExpression = Forward()
        and_ = CaselessLiteral("and")
        or_  = CaselessLiteral("or")
        in_  = CaselessLiteral("in")

        E = CaselessLiteral("E")
        binop = oneOf("= != < > >= <= eq ne lt le gt ge like", caseless=True)
        arithSign = Word("+-",exact=1)
        realNum = Combine( Optional(arithSign) + ( Word( nums ) + "." + Optional( Word(nums) )  |
            ( "." + Word(nums) ) ) + 
            Optional( E + Optional(arithSign) + Word(nums) ) )
        intNum = Combine( Optional(arithSign) + Word( nums ) + 
            Optional( E + Optional("+") + Word(nums) ) )

        columnRval = realNum | intNum | quotedString | columnName # need to add support for alg expressions
        whereCondition = Group(
            ( columnName + binop + columnRval ) |
            ( columnName + in_ + "(" + delimitedList( columnRval ) + ")" ) |
            ( columnName + in_ + "(" + sqlStmt + ")" ) |
            ( "(" + whereExpression + ")" )
            )
        whereExpression << whereCondition + ZeroOrMore( ( and_ | or_ ) + whereExpression ) 

        # define the grammar
        sqlStmt << ( (selectToken.setResultsName("command") + 
            ( '*' | columnNameList ).setResultsName( "columns" ) + 
            fromToken + tableNameList.setResultsName( "tables" ) + 
            Optional( Group( CaselessLiteral("where") + whereExpression ), "" ).setResultsName("where")) | 
            ( insertToken.setResultsName("command") + intoToken + tableNameList.setResultsName("tables") +
            Optional( "(" + columnNameList.setResultsName("columns") + ")") +
            valueToken + "(" + valueNameList.setResultsName("fields") + ")" +
            Optional( Group( CaselessLiteral("where") + whereExpression ), "" ).setResultsName("where")) |
            ( (createToken | dropToken).setResultsName("command") + typeNameList.setResultsName("type") +
            Optional(oneOf("' \"") + pathNameList.setResultsName("path") + oneOf("' \""))  +
            idNameList.setResultsName("id") +
            Optional("(" + varTypeNameList.setResultsName("var") + ")") ) )


        SQL = sqlStmt
        SQL.validate()

        # define Oracle comment format, and ignore them
        oracleSqlComment = "--" + restOfLine
        SQL.ignore( oracleSqlComment )

        self.grammar = SQL

class SQLParser:
    '''
    Test a simple select parsing:
    >>> import sqlparser
    >>> p = sqlparser.SQLParser()
    >>> tokens = p.parse("Select A, B, C from Sys.dual")
    >>> print tokens
    ['select', ['A', 'B', 'C'], 'from', ['SYS.DUAL'], '']
    >>> tokens.command
    'select'
    >>> tokens.tables
    (['SYS.DUAL'], {})
    >>> tokens.columns
    ([(['A', 'B', 'C'], {})], {})

    Test a simple insert parsing:
    >>> tokens = p.parse("Insert into sdfsdf(a,b,c) values(a,b,c)")
    >>> print tokens
    ['insert', 'into', ['SDFSDF'], '(', ['A', 'B', 'C'], ')', 'values', '(', ['A', 'B', 'C'], ')', '']
    >>> tokens.command
    'insert'
    >>> tokens.tables
    (['SDFSDF'], {})
    >>> tokens.columns
    (['A', 'B', 'C'], {})
    >>> tokens.fields
    (['A', 'B', 'C'], {})

    Test table create parsing:
    >>> tokens = p.parse('CREATE TABLE address_book (first_name VARCHAR(25), last_name VARCHAR(25), phone_number VARCHAR(15))')
    >>> tokens
    (['create', (['TABLE'], {}), (['ADDRESS_BOOK'], {}), '(', ([(['first_name', 'VARCHAR', '(', '25', ')'], {}), (['last_name', 'VARCHAR', '(', '25', ')'], {}), (['phone_number', 'VARCHAR', '(', '15', ')'], {})], {}), ')'], {'var': [(([(['first_name', 'VARCHAR', '(', '25', ')'], {}), (['last_name', 'VARCHAR', '(', '25', ')'], {}), (['phone_number', 'VARCHAR', '(', '15', ')'], {})], {}), 4)], 'type': [((['TABLE'], {}), 1)], 'command': [((['create'], {}), -1)], 'id': [((['ADDRESS_BOOK'], {}), 2)]})
    >>> tokens.command
    (['create'], {})
    >>> tokens.type
    (['TABLE'], {})
    >>> tokens.id
    (['ADDRESS_BOOK'], {})
    >>> tokens.var
    ([(['first_name', 'VARCHAR', '(', '25', ')'], {}), (['last_name', 'VARCHAR', '(', '25', ')'], {}), (['phone_number', 'VARCHAR', '(', '15', ')'], {})], {})

    Test database creation parsing:
    >>> tokens = p.parse("CREATE DATABASE '/desired/path/to/db'  MYDB")
    >>> print tokens
    ['create', ['DATABASE'], "'", ['/desired/path/to/db'], "'", ['MYDB']]
    >>> tokens.command
    (['create'], {})
    >>> tokens.type
    (['DATABASE'], {})
    >>> tokens.path
    (['/desired/path/to/db'], {})
    >>> tokens.id
    (['MYDB'], {})

    Alternate method of parsing database create:
    >>> p = sqlparser.SQLParser("CREATE DATABASE '/tmp'  testdb")
    >>> p.list_data
    ['create', (['DATABASE'], {}), "'", (['/tmp'], {}), "'", (['TESTDB'], {})]
    >>> p.dict_data
    {'command': (['create'], {}), 'type': (['DATABASE'], {}), 'id': (['TESTDB'], {}), 'path': (['/tmp'], {})}

    Test database drop:
    >>> p = sqlparser.SQLParser("DROP DATABASE '/tmp'  testdb")
    >>> p.list_data
    ['drop', (['DATABASE'], {}), "'", (['/tmp'], {}), "'", (['TESTDB'], {})]
    >>> p.dict_data
    {'command': (['drop'], {}), 'type': (['DATABASE'], {}), 'id': (['TESTDB'], {}), 'path': (['/tmp'], {})}
    
    Test table drop:
    >>> tokens = p.parse("DROP TABLE address_book")
    >>> tokens
    (['drop', (['TABLE'], {}), (['ADDRESS_BOOK'], {})], {'type': [((['TABLE'], {}), 1)], 'command': [((['drop'], {}), -1)], 'id': [((['ADDRESS_BOOK'], {}), 2)]})
    >>> tokens.command
    (['drop'], {})
    >>> tokens.type
    (['TABLE'], {})
    >>> tokens.id
    (['ADDRESS_BOOK'], {})
    
    '''

    def __init__(self, statement=None):
        sg = SQLGrammar()
        self.grammar = sg.grammar
        self.tokens = None
        self.list_data = []
        self.dict_data = {}
        if statement:
            self.parse(statement)

    def parse(self, sql_string):
        '''
        Take a sql string and return PyParser tokens as defined in the SQLGrammar class.
        '''
        tokens = self.grammar.parseString(sql_string)
        self.tokens = tokens
        self.list_data = list(tokens)
        self.dict_data = dict(tokens)
        return self.tokens


def _test():
    import doctest, sqlparser
    return doctest.testmod(sqlparser)

if __name__ == '__main__':
    _test()


